﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace Project_01
{
    class Database {
        private OleDbConnection db;
        public Database(String username, String password) {
            String file = "mess.mdb";
            db = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file);
        }
        public List<String> GetUsername(String filter) {
            String command =  "SELECT Username " +
                              "FROM Account, PublicInformation " + 
                              "WHERE Account.ID = PublicInformation.ID";
            if (filter.Length > 0) command += " AND Username LIKE '%" + filter + "%'";
            command += " ORDER BY State DESC";
            db.Open();
            OleDbDataReader reader = (new OleDbCommand(command, db)).ExecuteReader();
            List<String> list = new List<String>();
            while (reader.Read()) {
                list.Add(reader["Username"].ToString());
            }
            db.Close();
            return list;
        }
        public UserInfo GetUserInfo(String username) {
            String command =  "SELECT Username, Password, Gender.Name As Gender " +
                              "FROM Account, PublicInformation, Gender " + 
                              "WHERE Username = '" + username + "' AND " + 
                                    "Account.ID = PublicInformation.ID AND " + 
                                    "PublicInformation.Gender = Gender.ID";
            db.Open();
            OleDbDataReader reader = (new OleDbCommand(command, db)).ExecuteReader();
            UserInfo info = new UserInfo("", "", "");
            while (reader.Read()) {
                info = new UserInfo(reader["Username"].ToString(), reader["Password"].ToString(), reader["Gender"].ToString());
                System.Windows.Forms.MessageBox.Show(info.ToString());
            }
            db.Close();
            return info;
        }
        public void Connect() {
            throw new Exception("CONNECT");
        }
    }
}